package com.evil.application.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.evil.application.api.RemoteAppOrderOperationService;
import com.evil.application.convert.DataConvert;
import com.evil.application.enums.form.control.ControlCodeEnum;
import com.evil.application.enums.order.OrderSpecialControlEnum;
import com.evil.application.mapper.ApplicationInfoMapper;
import com.evil.application.pojo.dto.form.ControlDataRespDTO;
import com.evil.application.pojo.dto.form.control.ControlBaseInfoDTO;
import com.evil.application.pojo.dto.form.control.code.ControlDTO;
import com.evil.application.pojo.dto.form.control.code.config.CascadeSelectConfigDTO;
import com.evil.application.pojo.dto.form.control.option.FormOptionRespDTO;
import com.evil.application.pojo.dto.mq.ExcelExportMQReqDTO;
import com.evil.application.pojo.dto.mq.ExcelImportMQReqDTO;
import com.evil.application.pojo.dto.order.exp.ExcelExportResultDTO;
import com.evil.application.pojo.dto.order.exp.OrderExportReqDTO;
import com.evil.application.pojo.dto.order.imp.ExcelImportOrderDTO;
import com.evil.application.pojo.dto.order.imp.ExcelImportReqDTO;
import com.evil.application.pojo.dto.order.imp.ExcelImportResultDTO;
import com.evil.application.pojo.dto.queue.AddQueueReqDTO;
import com.evil.application.pojo.dto.queue.QueueContentDTO;
import com.evil.application.pojo.dto.queue.QueueRespDTO;
import com.evil.application.pojo.entity.ApplicationForm;
import com.evil.application.pojo.entity.ApplicationFormControl;
import com.evil.application.pojo.entity.ApplicationInfo;
import com.evil.application.service.*;
import com.evil.application.util.ApplicationUtil;
import com.evil.application.util.ExportOrderUtil;
import com.evil.common.account.dto.user.UserBaseRespDTO;
import com.evil.common.account.util.LoginUtil;
import com.evil.common.application.dto.ApplicationKeyReqDTO;
import com.evil.common.application.dto.order.OrderPageDataRespDTO;
import com.evil.common.application.dto.order.data.ControlDataDTO;
import com.evil.common.application.dto.order.data.ControlDataValueDTO;
import com.evil.common.application.enums.queue.QueueStatusEnum;
import com.evil.common.application.enums.queue.QueueTypeEnum;
import com.evil.common.core.entity.QueryModel;
import com.evil.common.core.enums.DateTypeEnum;
import com.evil.common.core.enums.RCodeEnum;
import com.evil.common.core.enums.SwitchEnum;
import com.evil.common.core.exception.BusinessException;
import com.evil.common.core.util.StreamUtil;
import com.evil.common.file.constant.OssConstant;
import com.evil.common.file.dto.FileRespDTO;
import com.evil.common.file.dto.UploadReqDTO;
import com.evil.common.file.enums.BusTypeEnum;
import com.evil.common.file.util.PoiUtil;
import com.evil.common.rocketmq.constant.MQConstant;
import com.evil.file.api.RemoteFileService;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.formula.BaseFormulaEvaluator;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.rocketmq.spring.core.RocketMQTemplate;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.NumberFormat;
import java.text.ParseException;
import java.util.*;
import java.util.function.BiConsumer;
import java.util.function.Predicate;
import java.util.stream.Collectors;

/**
 * 应用订单操作接口
 *
 * @author liyang
 * @date 2022-08-08 00:00
 */
@Slf4j
@RestController
@AllArgsConstructor
public class AppOrderOperationServiceImpl implements AppOrderOperationService, RemoteAppOrderOperationService {

    private final LoginUtil loginUtil;

    private final ApplicationInfoMapper applicationInfoMapper;

    private final ApplicationFormControlService applicationFormControlService;

    private final ApplicationOrderDataService applicationOrderDataService;

    private final ApplicationOrderService applicationOrderService;

    private final ApplicationQueueService applicationQueueService;

    private final ElasticsearchService elasticsearchService;

    private final RemoteFileService remoteFileService;

    private final RocketMQTemplate rocketMQTemplate;

    /**
     * 下载模板
     *
     * @param applicationKeyReqDTO applicationKeyReqDTO
     * @return ControlDataRespDTO
     */
    @Override
    public ControlDataRespDTO downloadTemplate(ApplicationKeyReqDTO applicationKeyReqDTO) {
        // 获取应用
        ApplicationInfo applicationInfo = applicationInfoMapper.findByKey(applicationKeyReqDTO.getApplicationKey());
        // 获取控件列表
        List<ControlBaseInfoDTO> controlBases = applicationFormControlService.findReleaseBaseInfoByApp(applicationInfo, true);
        // 导出模板时忽略特殊控件（申请人除外）
        controlBases = ApplicationUtil.unfoldControlChildren(controlBases, null, ControlCodeEnum.TABS).stream()
                .filter(c -> !OrderSpecialControlEnum.isSpecialControlId(c.getControlId()) || OrderSpecialControlEnum.CREATOR.getId().equals(c.getControlId()))
                .collect(Collectors.toList());
        return new ControlDataRespDTO(applicationInfo.getApplicationName(), controlBases);
    }

    /**
     * 获取应用模板名称
     *
     * @param applicationKey applicationKey
     * @return applicationName
     */
    @Override
    public String getAppTemplateName(String applicationKey) {
        return applicationInfoMapper.findByKey(applicationKey).getApplicationName();
    }

    /**
     * 应用订单导入
     *
     * @param excelImportReqDTO excelImportReqDTO
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void orderDataImport(ExcelImportReqDTO excelImportReqDTO) {
        Long loginEnterpriseId = loginUtil.getLoginEnterpriseId();
        Long loginUserId = loginUtil.getLoginUserId();

        String applicationKey = excelImportReqDTO.getApplicationKey();
        applicationInfoMapper.isExistByKey(applicationKey);

        // 上传文件 新增file数据
        UploadReqDTO uploadReqDTO = new UploadReqDTO();
        uploadReqDTO.setEnterpriseId(loginEnterpriseId);
        uploadReqDTO.setFile(excelImportReqDTO.getFile());
        // 应用订单导入文件
        uploadReqDTO.setType(BusTypeEnum.APPLICATION_ORDER_IMPORT_FILE.getId());
        uploadReqDTO.setUserId(loginUserId);
        uploadReqDTO.setAllowContentType(OssConstant.UPLOAD_EXCEL_TYPE);
        uploadReqDTO.setAllowMaxSize(OssConstant.UPLOAD_FILE_SIZE);
        FileRespDTO fileRespDTO = remoteFileService.uploadFile(uploadReqDTO);

        // 添加应用队列
        AddQueueReqDTO addQueueReqDTO = new AddQueueReqDTO();
        addQueueReqDTO.setEnterpriseId(loginEnterpriseId);
        addQueueReqDTO.setUserId(loginUserId);
        QueueTypeEnum queueTypeEnum = QueueTypeEnum.APP_ORDER_IMPORT;
        addQueueReqDTO.setQueueTitle(queueTypeEnum.getName());
        addQueueReqDTO.setQueueType(queueTypeEnum.getId());
        addQueueReqDTO.setQueueFileId(fileRespDTO.getFileId());
        QueueRespDTO queueRespDTO = applicationQueueService.addQueue(addQueueReqDTO);

        // 发起消息队列
        ExcelImportMQReqDTO excelImportMQReqDTO = new ExcelImportMQReqDTO();
        excelImportMQReqDTO.setFileId(fileRespDTO.getFileId());
        excelImportMQReqDTO.setApplicationKey(applicationKey);
        excelImportMQReqDTO.setQueueId(queueRespDTO.getQueueId());
        excelImportMQReqDTO.setEnterpriseId(loginEnterpriseId);
        rocketMQTemplate.convertAndSend(MQConstant.IMPORT_APPLICATION_ORDER_MQ_KEY, excelImportMQReqDTO);
    }

    /**
     * 获取订单控件数据列表
     *
     * @param queryModel queryModel
     */
    @Override
    public void orderDataExport(QueryModel<OrderExportReqDTO> queryModel) {
        if (!queryModel.getParam().getExportAll()) {
            if (CollectionUtil.isEmpty(queryModel.getParam().getOrderIds())) {
                throw new BusinessException(RCodeEnum.ERROR_PARAMETER, "请选择要导出的数据");
            }
        }
        Long loginEnterpriseId = loginUtil.getLoginEnterpriseId();
        Long loginUserId = loginUtil.getLoginUserId();
        String applicationKey = queryModel.getParam().getApplicationKey();
        applicationInfoMapper.isExistByKey(applicationKey);

        // 添加应用队列
        AddQueueReqDTO addQueueReqDTO = new AddQueueReqDTO();
        addQueueReqDTO.setEnterpriseId(loginEnterpriseId);
        addQueueReqDTO.setUserId(loginUserId);
        QueueTypeEnum queueTypeEnum = QueueTypeEnum.APP_ORDER_EXPORT;
        addQueueReqDTO.setQueueTitle(queueTypeEnum.getName());
        addQueueReqDTO.setQueueType(queueTypeEnum.getId());
        QueueRespDTO queueRespDTO = applicationQueueService.addQueue(addQueueReqDTO);

        // 发起消息队列
        ExcelExportMQReqDTO excelExportMQReqDTO = new ExcelExportMQReqDTO();
        excelExportMQReqDTO.setQueryModel(queryModel);
        excelExportMQReqDTO.setApplicationKey(applicationKey);
        excelExportMQReqDTO.setQueueId(queueRespDTO.getQueueId());
        excelExportMQReqDTO.setEnterpriseId(loginEnterpriseId);
        rocketMQTemplate.convertAndSend(MQConstant.EXPORT_APPLICATION_ORDER_MQ_KEY, excelExportMQReqDTO);
    }

    /**
     * 处理导入
     *
     * @param workbook        workbook
     * @param applicationForm applicationForm
     * @return ExcelImportResultDTO
     */
    @Override
    public ExcelImportResultDTO handleExcelImport(Workbook workbook, ApplicationForm applicationForm) {
        // 获取表单控件列表
        List<ApplicationFormControl> controls = applicationFormControlService.findByForm(applicationForm, true);
        // 控件map
        Map<Long, ApplicationFormControl> controlMap = StreamUtil.toMapK(controls, ApplicationFormControl::getControlId);
        // 不导入的特殊字段
        List<Long> unImportSpecialControlIds = OrderSpecialControlEnum.getCanUnImportControlIds();
        // 订单控件信息
        List<ControlDTO> controlDTOs = applicationFormControlService.transDTO(controls).stream()
                // 过滤 不导入的特殊字段
                .filter(f -> !unImportSpecialControlIds.contains(f.getControlId()))
                .collect(Collectors.toList());
        // 层级分类
        controlDTOs = ApplicationUtil.classifyControl(controlDTOs);

        // 检验工作簿并且返回标题行
        Integer titleRow = this.verifyWorkbook(workbook, controlDTOs);
        // 只导入第一个sheet
        Sheet sheet = workbook.getSheetAt(0);

        // 获取控件选项map
        Map<Long, Map<String, FormOptionRespDTO>> controlOptionMap = ApplicationUtil.getOptionMapForControls(controlDTOs);

        // 构造 订单数据转换 处理器
        DataConvert dataConvert = new DataConvert(loginUtil)
                .initDepartmentMap()
                .initUserMap();

        // 公式运算
        BaseFormulaEvaluator baseFormulaEvaluator = PoiUtil.getFormulaEvaluator(workbook);
        // 数据初始行
        int rowNum = titleRow + 1;
        // 获取excel表中的最后一行
        int lastRowNum = sheet.getLastRowNum();
        // 合并单元格所跨的行数
        int mergeRowNum = 1;
        // 记录第几列是第一个非表格控件
        int notTableColumn = this.getFirstUnTableCellNum(controlDTOs);
        // 是否有错误
        boolean hasError = false;
        // 其他错误信息（备注：模板内错误，已写入模板）
        String otherErrorMsg = null;

        // 导入订单列表
        List<ExcelImportOrderDTO> importOrders = new ArrayList<>();
        // 循环获取行数据
        for (int r = rowNum; r <= lastRowNum; r++) {
            List<ControlDataDTO> controlDataList = new ArrayList<>();
            // 获取每行数据
            Row initRow = sheet.getRow(r);
            // 定义当前列
            int nowColumn = 0;
            // 申请人
            Long creatorUserId = null;
            if (null != initRow) {
                // 获取合并单元格所跨的行数
                mergeRowNum = PoiUtil.getMergeIndex(sheet, r, notTableColumn);
                // 获取每行每个单元格的控件名称和对应的值
                for (int c = 0; c <= controlDTOs.size() - 1; c++) {
                    ControlDTO controlDTO = controlDTOs.get(c);

                    // 如果是表格控件，遍历子控件
                    if (controlDTO.getControlCode().equals(ControlCodeEnum.TABLE.getCode())) {
                        // 表格行
                        int tableFirstRowNum = r;
                        // 表格控件数据
                        ControlDataDTO tableControlData = BeanUtil.copyProperties(controlDTO, ControlDataDTO.class);

                        // 获取子级控件选项map
                        Map<Long, Map<String, FormOptionRespDTO>> tableControlOptionMap = ApplicationUtil.getOptionMapForControls(controlDTO.getChildControls());

                        int tableFirstColumnNum;
                        int count = 0;
                        for (int i = 0; i < mergeRowNum; i++) {
                            tableFirstColumnNum = nowColumn;
                            List<ControlDataDTO> tableControlDataList = new ArrayList<>();
                            // 获取每行数据
                            Row tableRow = sheet.getRow(tableFirstRowNum);

                            for (ControlDTO childControl : controlDTO.getChildControls()) {
                                // 获取单元格
                                Cell cell = PoiUtil.getCell(tableRow, tableFirstColumnNum);
                                // 获取单元格值
                                Object value = this.getCellValue(cell, baseFormulaEvaluator);
                                // 控件数据DTO
                                ControlDataDTO controlValueDTO = ApplicationUtil.controlToControlData(childControl);
                                try {
                                    // 校验单元格中的value
                                    List<ControlDataValueDTO> controlDataValueList = this.handleCellValue(
                                            controlMap.get(childControl.getControlId()),
                                            value,
                                            dataConvert,
                                            tableControlOptionMap.get(controlValueDTO.getControlId())
                                    );
                                    // 封装数据
                                    controlValueDTO.setValues(controlDataValueList);
                                } catch (Exception exception) {
                                    log.error("【应用订单处理导入】，异常：", exception);
                                    hasError = true;
                                    String msg = null == exception.getMessage() ? RCodeEnum.ERROR_RUNTIME.getMessage() : exception.getMessage();
                                    PoiUtil.addErrorComment(cell, msg, 2);
                                    continue;
                                }
                                tableControlDataList.add(controlValueDTO);
                                tableFirstColumnNum++;
                                if (i == 0) {
                                    count++;
                                }
                            }
                            tableControlData.getTableValues().add(tableControlDataList);
                            tableFirstRowNum++;
                        }
                        nowColumn += count;
                        controlDataList.add(tableControlData);
                    } else {
                        // 获取单元格
                        Cell cell = PoiUtil.getCell(initRow, nowColumn);
                        // 获取单元格值
                        Object value = this.getCellValue(cell, baseFormulaEvaluator);
                        // 控件数据DTO
                        ControlDataDTO controlValueDTO = ApplicationUtil.controlToControlData(controlDTO);
                        try {
                            // 校验单元格中的value
                            List<ControlDataValueDTO> controlDataValueList = this.handleCellValue(
                                    controlMap.get(controlDTO.getControlId()),
                                    value,
                                    dataConvert,
                                    controlOptionMap.get(controlValueDTO.getControlId())
                            );
                            // 封装数据
                            controlValueDTO.setValues(controlDataValueList);

                            // 申请人特殊控件单独处理
                            if (controlDTO.getControlId().equals(OrderSpecialControlEnum.CREATOR.getId())) {
                                Optional<Long> creatorOptional = controlDataValueList.stream().map(ControlDataValueDTO::getId).findFirst();
                                if (creatorOptional.isPresent()) {
                                    creatorUserId = creatorOptional.get();
                                } else {
                                    hasError = true;
                                    otherErrorMsg = "导入模板错误";
                                }
                            }
                        } catch (Exception exception) {
                            log.error("【应用订单处理导入】，异常：", exception);
                            hasError = true;
                            String msg = null == exception.getMessage() ? RCodeEnum.ERROR_RUNTIME.getMessage() : exception.getMessage();
                            PoiUtil.addErrorComment(cell, msg, 2);
                            continue;
                        }
                        nowColumn++;
                        controlDataList.add(controlValueDTO);
                    }
                }
            }

            if (!hasError) {
                // 申请人数据为空 导入模板 错误
                if (null == creatorUserId) {
                    hasError = true;
                    otherErrorMsg = "导入模板错误";
                }
                importOrders.add(new ExcelImportOrderDTO(creatorUserId, controlDataList));
            }
            r += mergeRowNum - 1;
        }

        // 返回结果数据
        return new ExcelImportResultDTO(hasError, otherErrorMsg, importOrders);
    }

    /**
     * 导出处理
     *
     * @param queryModel  导出参数
     * @param handleQueue 队列同步处理
     * @return FileRespDTO
     */
    @Override
    public FileRespDTO excelExport(QueryModel<OrderExportReqDTO> queryModel, BiConsumer<QueueStatusEnum, QueueContentDTO> handleQueue) {
        OrderExportReqDTO param = queryModel.getParam();
        // 获取应用
        ApplicationInfo applicationInfo = applicationInfoMapper.findByKey(param.getApplicationKey());

        String fileName = StringUtils.defaultIfBlank(param.getFileName(), applicationInfo.getApplicationName());

        //  若没有选择展示字段，则展示全部字段，若有选择字段，只展示选中的字段
        List<ControlBaseInfoDTO> controlBases = applicationFormControlService.findReleaseBaseInfoByApp(applicationInfo, true);
        // 过滤可见控件
        controlBases = ApplicationUtil.unfoldControlChildren(controlBases, param.getControlIds(), ControlCodeEnum.TABS);

        // 若没有选择订单则导出全部（此时页尺=总条数）；若有选择订单，则从当前页过滤要导出的订单
        List<OrderPageDataRespDTO> orders = applicationOrderService.orderList(queryModel.trans(param)).getList();
        if (!param.getExportAll()) {
            if (Objects.isNull(param.getOrderIds())) {
                param.setOrderIds(new ArrayList<>());
            }
            orders = orders.stream().filter(order -> param.getOrderIds().contains(order.getOrderId())).collect(Collectors.toList());
        }

        // 处理导出的控件 数据
        for (OrderPageDataRespDTO order : orders) {
            Map<Long, ControlDataDTO> controlDataMap = StreamUtil.toMapK(order.getData(), ControlDataDTO::getControlId);
            order.setData(controlBases.stream()
                    // 如果控件没有值，则生成空数据实体
                    .map(e -> controlDataMap.getOrDefault(e.getControlId(), ApplicationUtil.controlToControlData(e)))
                    .collect(Collectors.toList()));
        }
        // 创建文件及对应的工作簿
        Workbook workbook = ExportOrderUtil.createAppOrderWorkbook(controlBases, fileName);
        // 处理导出
        ExcelExportResultDTO excelExportResultDTO = this.handleExcelExport(workbook, controlBases, orders, param.getExportAnnex());

        // 处理导出结果
        MultipartFile file = this.handleExportResult(excelExportResultDTO.getFile(), workbook, fileName);

        // 上传文件 新增file数据
        UploadReqDTO uploadReqDTO = new UploadReqDTO();
        uploadReqDTO.setEnterpriseId(loginUtil.getLoginEnterpriseId());
        uploadReqDTO.setFile(file);
        uploadReqDTO.setType(BusTypeEnum.APPLICATION_EXPORT_FILE.getId());
        uploadReqDTO.setUserId(loginUtil.getLoginUserId());
        uploadReqDTO.setAllowContentType(OssConstant.UPLOAD_ALLOW_MIME_TYPE);
        uploadReqDTO.setAllowMaxSize(OssConstant.UPLOAD_FILE_SIZE);
        FileRespDTO fileRespDTO = remoteFileService.uploadFile(uploadReqDTO);

        // 队列处理
        if (Objects.nonNull(handleQueue)) {
            QueueStatusEnum queueStatusEnum = excelExportResultDTO.getHasError() ? QueueStatusEnum.FAILED : QueueStatusEnum.COMPLETE;
            QueueContentDTO queueContentDTO = new QueueContentDTO(fileRespDTO.getFileCode(), excelExportResultDTO.getOtherErrorMsg());
            handleQueue.accept(queueStatusEnum, queueContentDTO);
        }
        return fileRespDTO;
    }

    @Override
    public ExcelExportResultDTO handleExcelExport(Workbook workbook, List<ControlBaseInfoDTO> controlBases, List<OrderPageDataRespDTO> orders, boolean exportAnnex) {
        Sheet sheet = workbook.getSheetAt(0);
        // 没有表头数据，导出空文件
        if (CollectionUtil.isEmpty(controlBases)) {
            return new ExcelExportResultDTO(false, null, null);
        }
        // 数据样式
        CellStyle dataStyle = PoiUtil.createdCellStyle(workbook, (short) 8, false, true);
        // 创建表头
        int rowNum = controlBases.stream().noneMatch(c -> ControlCodeEnum.TABLE.getCode().equals(c.getControlCode())) ? 0 : 1;
        // 没有数据，导出空文件
        if (CollectionUtil.isEmpty(orders)) {
            return new ExcelExportResultDTO(false, null, null);
        }
        // 表格子级数量
        Map<Long, Integer> tableSizeMap = StreamUtil.toMapFKV(
                controlBases,
                c -> ControlCodeEnum.TABLE.getCode().equals(c.getControlCode()),
                ControlBaseInfoDTO::getControlId,
                c -> c.getChildControls().size()
        );
        try {
            File folder = this.handleExportData(orders, rowNum, sheet, dataStyle, exportAnnex, tableSizeMap);
            return new ExcelExportResultDTO(false, null, folder);
        } catch (Exception e) {
            log.error("【导出应用订单】，异常：", e);
            return new ExcelExportResultDTO(true, e.getMessage(), null);
        }
    }

    /**
     * 校验 workbook
     *
     * @param workbook workbook
     * @param controls 控件列表
     * @return 有效数据行
     */
    private Integer verifyWorkbook(Workbook workbook, List<ControlDTO> controls) {
        // 校验
        if (null == workbook) {
            throw new BusinessException(RCodeEnum.IMPORT_TEMPLATE_ERROR);
        }
        // 获取第一个表格
        Sheet sheet = workbook.getSheetAt(0);
        if (null == sheet) {
            throw new BusinessException(RCodeEnum.IMPORT_TEMPLATE_ERROR);
        }
        // 获取工作簿中的第1行 标题
        Row titleFirstRow = sheet.getRow(0);
        // 获取工作簿中的第2行 标题
        Row titleSecondRow = sheet.getRow(1);
        Predicate<ControlDTO> isTable = c -> ControlCodeEnum.TABLE.getCode().equals(c.getControlCode());
        Optional<ControlDTO> hasTable = StreamUtil.isExistFirst(controls, isTable);
        if (null == titleFirstRow || (hasTable.isPresent() && null == titleSecondRow)) {
            throw new BusinessException(RCodeEnum.IMPORT_TEMPLATE_ERROR);
        }
        int columnNum = 0;
        for (ControlDTO control : controls) {
            if (isTable.test(control)) {
                for (ControlDTO childControl : control.getChildControls()) {
                    if (!StringUtils.equals(titleSecondRow.getCell(columnNum).getStringCellValue(), childControl.getControlTitle())) {
                        throw new BusinessException(RCodeEnum.IMPORT_TEMPLATE_ERROR);
                    }
                    columnNum++;
                }
            } else {
                if (!StringUtils.equals(titleFirstRow.getCell(columnNum).getStringCellValue(), control.getControlTitle())) {
                    throw new BusinessException(RCodeEnum.IMPORT_TEMPLATE_ERROR);
                }
                columnNum++;
            }
        }
        return hasTable.isPresent() ? 1 : 0;
    }

    /**
     * 获取第一个非表格列号（不存在返回-1）
     *
     * @param controls controls
     * @return int
     */
    private int getFirstUnTableCellNum(List<ControlDTO> controls) {
        // 记录第几列是第一个非表格控件
        int notTableColumn = -1;
        // 读取第一个非表格控件的单元格所占行数
        int index = 0;
        for (ControlDTO control : controls) {
            if (!control.getControlCode().equals(ControlCodeEnum.TABLE.getCode())) {
                notTableColumn = index;
                break;
            } else {
                index += control.getChildControls().size();
            }
        }
        return notTableColumn;
    }

    /**
     * 获取单元格的值
     *
     * @param cell                 单元格
     * @param baseFormulaEvaluator 公式运算器
     * @return Object
     */
    private Object getCellValue(Cell cell, BaseFormulaEvaluator baseFormulaEvaluator) {
        // 获取单元格值
        cell = baseFormulaEvaluator.evaluateInCell(cell);
        Object value = PoiUtil.getCellValue(cell);
        CellType cellType = cell.getCellType();
        if (Objects.isNull(value)) {
            value = "";
        } else {
            if (StringUtils.isNotBlank(value.toString()) && cellType != CellType.STRING) {
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else {
                    NumberFormat numberFormat = NumberFormat.getInstance();
                    // 不显示千位分割符，否则显示结果会变成类似1,234,567,890
                    numberFormat.setGroupingUsed(false);
                    value = numberFormat.format(cell.getNumericCellValue());
                }
            }
        }
        return value;
    }

    /**
     * 检验导入模板数据
     *
     * @param control     control
     * @param value       value
     * @param dataConvert dataConvert
     * @param optionMap   optionMap
     * @return List
     */
    private List<ControlDataValueDTO> handleCellValue(ApplicationFormControl control, Object value, DataConvert dataConvert, Map<String, FormOptionRespDTO> optionMap) {
        List<ControlDataValueDTO> controlDataValueList = new ArrayList<>();
        String valueError = String.format("[%s]控件值错误", control.getControlTitle());
        if (StringUtils.isBlank(value.toString())) {
            return controlDataValueList;
        }
        // 获取控件类型
        ControlCodeEnum controlCodeEnum = ControlCodeEnum.findByCode(control.getControlCode());

        // 分隔符
        String separator = StringUtils.defaultIfBlank(control.getSelectSeparator(), PoiUtil.IMP_EXP_SEPARATORS);
        // 单值
        if (controlCodeEnum.isSingle()) {
            // 下拉单选 || 引用
            if (controlCodeEnum.hasOptions() || controlCodeEnum.hasQuote()) {
                // 判断是否存在
                if (optionMap.containsKey(value.toString())) {
                    FormOptionRespDTO option = optionMap.get(value.toString());
                    controlDataValueList.add(new ControlDataValueDTO(option.getOptionId(), option.getOptionName()).setOtherInfo(option));
                }
            }
            // 日期
            else if (controlCodeEnum.isDate()) {
                DateTypeEnum dateTypeEnum = DateTypeEnum.isExistById(control.getDateType());
                if (ControlCodeEnum.DATE_RANGE_PICKER != controlCodeEnum) {
                    String date = null;
                    if (ObjectUtil.isNotEmpty(value)) {
                        try {
                            date = dateTypeEnum.dateFormat(value);
                        } catch (ParseException e) {
                            throw new BusinessException(RCodeEnum.ERROR_RUNTIME, valueError);
                        }
                    }
                    controlDataValueList.add(new ControlDataValueDTO(date));
                } else {
                    this.splitValue(value.toString(), separator).forEach(vs -> {
                        try {
                            controlDataValueList.add(new ControlDataValueDTO(dateTypeEnum.dateFormat(vs)));
                        } catch (ParseException e) {
                            throw new BusinessException(RCodeEnum.ERROR_RUNTIME, valueError);
                        }
                    });
                }
            }
            // 普通单值
            else {
                controlDataValueList.add(new ControlDataValueDTO(value.toString()));
            }
        }
        // 多值
        else if (controlCodeEnum.isMultiple()) {
            // 下拉多选 || 关联多选
            if (controlCodeEnum.hasOptions()) {
                boolean cascadeMultiple = false;
                if (ControlCodeEnum.CASCADE.getCode().equals(controlCodeEnum.getCode())) {
                    CascadeSelectConfigDTO cascadeSelectConfigDTO = JSON.parseObject(control.getCascadeSelectConfig(), CascadeSelectConfigDTO.class);
                    // 级联多选 && 显示完整路径 && 非任选层级
                    if (SwitchEnum.isOpen(control.getMultiple())
                            && cascadeSelectConfigDTO.getShowAllLevels()
                            && !cascadeSelectConfigDTO.getCheckStrictly()) {
                        // 级联多选 需要特殊处理
                        cascadeMultiple = true;
                    }
                }
                if (cascadeMultiple) {
                    this.splitValue(value.toString(), PoiUtil.IMP_EXP_SEPARATORS).forEach(vs -> {
                        ControlDataValueDTO controlDataValueDTO = new ControlDataValueDTO();
                        // 只获取匹配得上的id数组
                        List<Long> ids = Arrays.stream(vs.split(separator)).filter(optionMap::containsKey).map(v -> optionMap.get(v).getOptionId()).collect(Collectors.toList());
                        controlDataValueDTO.setIds(ids);
                        controlDataValueList.add(controlDataValueDTO);
                    });
                } else {
                    this.splitValue(value.toString(), separator).forEach(vs -> {
                        // 判断是否存在
                        if (optionMap.containsKey(vs)) {
                            FormOptionRespDTO option = optionMap.get(vs);
                            controlDataValueList.add(new ControlDataValueDTO(option.getOptionId(), option.getOptionName()).setOtherInfo(option));
                        }
                    });
                }
            }
            // 部门多选
            else if (ControlCodeEnum.DEPT_SEL == controlCodeEnum) {
                dataConvert.getDepartmentBaseByNames(this.splitValue(value.toString(), separator)).forEach(d ->
                        controlDataValueList.add(new ControlDataValueDTO(d.getDepartmentId(), d.getDepartmentName()).setOtherInfo(d)));
            }
            // 成员多选
            else if (ControlCodeEnum.MEMBER_SEL == controlCodeEnum) {
                if (OrderSpecialControlEnum.CREATOR.getId().equals(control.getControlId())) {
                    // 申请人
                    try {
                        UserBaseRespDTO creator = dataConvert.getUserBaseByName(value.toString());
                        controlDataValueList.add(new ControlDataValueDTO(creator.getUserId(), creator.getNickName()).setOtherInfo(creator));
                    } catch (Exception e) {
                        throw new BusinessException(RCodeEnum.IMPORT_ERROR, "申请人不存在");
                    }
                } else {
                    dataConvert.getUserBasesByNames(this.splitValue(value.toString(), separator)).forEach(u ->
                            controlDataValueList.add(new ControlDataValueDTO(u.getUserId(), u.getNickName()).setOtherInfo(u)));
                }
            }
            // 普通多值
            else {
                this.splitValue(value.toString(), separator).forEach(vs -> controlDataValueList.add(new ControlDataValueDTO(vs)));
            }
        }
        return controlDataValueList;
    }

    /**
     * 分割值
     *
     * @param value     value
     * @param separator separator
     * @return List
     */
    private List<String> splitValue(String value, String separator) {
        if (StringUtils.isNotBlank(value)) {
            return Arrays.asList(value.split(separator));
        }
        return new ArrayList<>();
    }

    /**
     * 处理到处数据（如果需要导出表单中附件，则返回压缩包文件；否则返回null）
     *
     * @param orders       导出订单数据
     * @param rowNum       表头行数
     * @param sheet        工作簿
     * @param dataStyle    数据样式
     * @param exportAnnex  是否导出表单附件
     * @param tableSizeMap 表格子级数量map
     * @return 压缩包文件
     */
    private File handleExportData(List<OrderPageDataRespDTO> orders, int rowNum, Sheet sheet, CellStyle dataStyle, boolean exportAnnex, Map<Long, Integer> tableSizeMap) {
        // 遍历订单数量
        int initRow = rowNum + 1;
        // 创建临时文件夹
        File tmpDir = null;
        // 记录订单号
        List<String> orderSnList = new ArrayList<>();
        for (OrderPageDataRespDTO order : orders) {
            // 订单号：保证文件夹不重复
            String orderSn = this.formatFolderName(orderSnList, order.getOrderSn());
            // 创建行
            Row row = sheet.createRow(initRow);
            // 列号
            int cellNum = 0;
            // 获取表格最大行数
            int tableMaxRows = order.getData().stream().map(d -> d.getTableValues().size()).max(Integer::compareTo).orElse(0);
            // 记录表格开始行号
            int firstTableRows;
            Map<Integer, Row> tableRowMap = new HashMap<>();
            // 记录文件控件名称
            List<String> fileControlTitles = new ArrayList<>();
            for (ControlDataDTO item : order.getData()) {
                // 如果是表格控件
                if (ControlCodeEnum.TABLE.getCode().equals(item.getControlCode())) {
                    firstTableRows = initRow;
                    // 当前列号
                    int tableCellNum;
                    Row tableRow;
                    boolean first = true;
                    for (List<ControlDataDTO> tableValue : item.getTableValues()) {
                        // 当前行
                        if (first) {
                            tableRow = row;
                        } else {
                            if (tableRowMap.containsKey(firstTableRows)) {
                                tableRow = tableRowMap.get(firstTableRows);
                            } else {
                                tableRow = sheet.createRow(firstTableRows);
                                tableRowMap.put(firstTableRows, tableRow);
                            }
                        }
                        // 当前列号
                        tableCellNum = cellNum;
                        for (ControlDataDTO controlDataDTO : tableValue) {
                            Cell cell = tableRow.createCell(tableCellNum);
                            cell.setCellStyle(dataStyle);
                            File file = this.handleCell(controlDataDTO, cell, exportAnnex, fileControlTitles, orderSn, tmpDir);
                            tmpDir = ObjectUtil.defaultIfNull(file, tmpDir);
                            tableCellNum++;
                        }
                        first = false;
                        firstTableRows++;
                    }
                    // 表格结束，列表=+表格子级数量
                    cellNum += tableSizeMap.get(item.getControlId());
                } else {
                    Cell cell = row.createCell(cellNum);
                    cell.setCellStyle(dataStyle);
                    // 合并单元格
                    if (tableMaxRows > 1) {
                        sheet.addMergedRegion(new CellRangeAddress(initRow, initRow + tableMaxRows - 1, cellNum, cellNum));
                    }
                    File file = this.handleCell(item, cell, exportAnnex, fileControlTitles, orderSn, tmpDir);
                    tmpDir = ObjectUtil.defaultIfNull(file, tmpDir);
                    cellNum++;
                }
            }
            // 每条订单循环完成后的结束行号,也是下一条订单的起始行号
            initRow += tableMaxRows > 0 ? tableMaxRows : 1;
        }
        return tmpDir;
    }

    /**
     * 处理单元格
     *
     * @param item              控件值 实体
     * @param cell              单元格
     * @param exportAnnex       是否导出附件
     * @param fileControlTitles 文件控件名称列表
     * @param orderSn           订单编号
     * @param tmpDir            临时文件夹
     * @return File
     */
    private File handleCell(ControlDataDTO item, Cell cell, boolean exportAnnex, List<String> fileControlTitles, String orderSn, File tmpDir) {
        // 通过控件code获取控件枚举
        ControlCodeEnum controlCodeEnum = ControlCodeEnum.findByCode(item.getControlCode());
        if (ControlCodeEnum.UPLOAD == controlCodeEnum) {
            // 上传控件
            if (exportAnnex && !item.getValues().isEmpty()) {
                List<FileRespDTO> files = item.getValues().stream().map(e -> BeanUtil.copyProperties(e.getOtherInfo(), FileRespDTO.class)).collect(Collectors.toList());
                return this.handleZipFile(files, fileControlTitles, item.getControlTitle(), orderSn, tmpDir);
            }
        } else {
            String value = controlCodeEnum.getControlDataToString().apply(item);
            if (!OrderSpecialControlEnum.isSpecialControlId(item.getControlId()) && ControlCodeEnum.DATE_PICKER == controlCodeEnum && StringUtils.isNotBlank(value)) {
                ApplicationFormControl control = applicationFormControlService.findById(item.getControlId());
                value = DateTypeEnum.isExistById(control.getDateType()).dateFormatIgnoreError(value);
            }
            cell.setCellValue(StrUtil.NULL.equals(value) ? StrUtil.EMPTY : value);
        }
        return null;
    }

    /**
     * 处理压缩文件
     *
     * @param files                文件对象列表
     * @param fileControlTitleList fileControlTitleList
     * @param title                标题
     * @param orderSn              订单编号
     * @param tmpDir               临时文件夹
     * @return File
     */
    private File handleZipFile(List<FileRespDTO> files, List<String> fileControlTitleList, String title, String orderSn, File tmpDir) {
        // 控件名称：保证文件夹不重复
        String controlTitle = this.formatFolderName(fileControlTitleList, title);

        // 记录文件名称
        List<String> fileNames = new ArrayList<>();
        for (FileRespDTO fileRespDTO : files) {
            // 控件名称：保证文件夹不重复
            String fileName = this.formatFolderName(fileNames, fileRespDTO.getFileName());
            try {
                // 接收文件byte数据
                byte[] fileBytes = remoteFileService.getFileByteByFileBase(fileRespDTO);

                // 初始化临时目录文件夹
                if (null == tmpDir) {
                    tmpDir = FileUtil.file(String.format("%s/%s", FileUtil.getTmpDirPath(), System.currentTimeMillis()));
                }
                // 初始化订单文件夹
                File orderFolder = FileUtil.mkdir(String.format("%s/%s", tmpDir.getPath(), orderSn));
                // 初始化控件文件夹
                File controlFolder = FileUtil.mkdir(String.format("%s/%s", orderFolder.getPath(), controlTitle));
                // 创建接收文件
                File file = new File(String.format("%s/%s", controlFolder.getPath(), fileName));

                OutputStream out = new FileOutputStream(file);
                if (fileBytes != null) {
                    FileUtil.writeBytes(fileBytes, file);
                } else {
                    out.close();
                    FileUtil.del(file);
                }
                out.close();
            } catch (Exception e) {
                throw new BusinessException(RCodeEnum.ERROR_RUNTIME);
            }
        }
        return tmpDir;
    }

    /**
     * 格式化文件夹名称
     *
     * @param folderNames folderNames
     * @param folderName  folderName
     * @return String
     */
    private String formatFolderName(List<String> folderNames, String folderName) {
        int i = 1;
        folderName = folderName.replaceAll("[\\\\/:*?\"<>|]", "_");
        while (folderNames.contains(folderName)) {
            folderName = String.format("%s_%s", folderName, i);
            i++;
        }
        folderNames.add(folderName);
        return folderName;
    }

    /**
     * 处理导出结果压缩文件夹/工作簿 为MultipartFile
     *
     * @param folder          压缩文件夹
     * @param workbook        工作簿
     * @param applicationName 应用名称
     * @return MultipartFile
     */
    private MultipartFile handleExportResult(File folder, Workbook workbook, String applicationName) {
        try {
            if (null != folder) {
                File zip = PoiUtil.zipWorkbook(folder, workbook, applicationName, true);
                return PoiUtil.fileToMultipartFile(zip, OssConstant.ZIP_FILE_CONTENT_TYPE);
            } else {
                return PoiUtil.workbookToMultipartFile(workbook, applicationName, true);
            }
        } catch (Exception e) {
            throw new BusinessException(RCodeEnum.EXPORT_ERROR);
        }
    }
}
